package dao.impl;

import bean.Student;
import dao.StudentDao;
import util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class StudentIDaoImp implements StudentDao {

    Connection con = null;

    Student student = null;

    PreparedStatement ps = null;

    ResultSet rs = null;

    int row = 0;

    /***
     * 获取学生信息
     * @return
     */
    @Override
    public List<Student> getStudentsAll() {
        List<Student> studentsList = new ArrayList<Student>();
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from student";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                student = new Student();
                student.setSno(rs.getString("Sno"));
                student.setSname(rs.getString("Sname"));
                student.setSage(rs.getString("Sage"));
                student.setSdept(rs.getString("Sdept"));
                student.setSsex(rs.getString("Ssex"));
                studentsList.add(student);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return studentsList;
    }

    @Override
    public Student getStudentsById(String Son) {
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from student where Sno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, Son);
            rs = ps.executeQuery();
            while (rs.next()){
                student = new Student();
                student.setSno(rs.getString("Sno"));
                student.setSname(rs.getString("Sname"));
                student.setSage(rs.getString("Sage"));
                student.setSdept(rs.getString("Sdept"));
                student.setSsex(rs.getString("Ssex"));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return student;
    }

    @Override
    public List<Student> getStudentsByName(String Sname) {
        List<Student> studentsList = new ArrayList<Student>();
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from student where Sname like ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%"+Sname+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                student = new Student();
                student.setSno(rs.getString("Sno"));
                student.setSname(rs.getString("Sname"));
                student.setSage(rs.getString("Sage"));
                student.setSdept(rs.getString("Sdept"));
                student.setSsex(rs.getString("Ssex"));
                studentsList.add(student);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return studentsList;
    }

    @Override
    public int addStudent(Student student) {
        try {
            con = JdbcUtil.getCon();
            String sql = "insert into student values(?,?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, student.getSno());
            ps.setString(2, student.getSname());
            ps.setString(3, student.getSsex());
            ps.setString(4, student.getSage());
            ps.setString(5, student.getSdept());
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public int updateStudent(Student student) {
        try {
            con = JdbcUtil.getCon();
            String sql = "update student set Sname = ?,Sage = ?,Sdept = ?,Ssex = ? where Sno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, student.getSname());
            ps.setString(2, student.getSage());
            ps.setString(3, student.getSdept());
            ps.setString(4, student.getSsex());
            ps.setString(5, student.getSno());
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public int deleteStudent(String Son) {
        try {
            con = JdbcUtil.getCon();
            String sql = "delete from student where Sno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, Son);
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }


}
